﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using DevExpress.XtraEditors;
using Excel = Microsoft.Office.Interop.Excel;

namespace QuanLyCongDan.GUI
{
    public partial class frmThemTuExcel : DevExpress.XtraEditors.XtraForm
    {
        private string maDonVi = string.Empty;
        
        public frmThemTuExcel()
        {
            InitializeComponent();
            cbbDonVi.DataSource = DAL.DAL_DonVi.GetListDonVi();
            cbbDonVi.DisplayMember = "TenDonVi";
            cbbDonVi.ValueMember = "MaDonVi";
        }

        private void btnBrowse_Click(object sender, EventArgs e)
        {
            try
            {
                OpenFileDialog of = new OpenFileDialog();
                of.Filter = "Excel 2003|*.xls";
                if (of.ShowDialog() == DialogResult.OK)
                {
                    txtDuongDan.Text = of.FileName;
                                      
                }
            }
            catch (Exception ex)
            {
                //MessageBox.Show(ex.Message);
                MessageBox.Show("Có lỗi xảy ra");
                //btnNhapLieu.Enabled = false;
            }
        }

        private void btnNhapLieu_Click(object sender, EventArgs e)
        {
            if (string.IsNullOrEmpty(txtDuongDan.Text))
            {
                MessageBox.Show("Hãy chọn file cần nhập vào");
                return;
            }
            if (string.IsNullOrEmpty(txtDongDauTien.Text))
            {
                MessageBox.Show("Nhập vào vị trí dòng bắt đầu");
                return;
            }
            if (string.IsNullOrEmpty(txtDongCuoiCung.Text))
            {
                MessageBox.Show("Nhập vào vị trí dòng kết thúc");
                return;
            }
            try
            {
                Excel.Application xlApp;
                Excel.Workbook xlWorkBook;
                Excel.Worksheet xlWorkSheet;
                Excel.Range range;

                xlApp = new Excel.ApplicationClass();
                xlWorkBook = xlApp.Workbooks.Open(txtDuongDan.Text, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

                range = xlWorkSheet.UsedRange;
                List<DTO.DTO_CongDan> arrCD = new List<QuanLyCongDan.DTO.DTO_CongDan>();
                maDonVi = ((DTO.DTO_DonVi)cbbDonVi.SelectedItem).MaDonVi;
                int dongdautien = Convert.ToInt32(txtDongDauTien.Text);
                int dongketthuc = Convert.ToInt32(txtDongCuoiCung.Text);
                List<DTO.DTO_Field> listFieldDonVi = DAL.DAL_Field.GetListFieldDonVi(((DTO.DTO_DonVi)cbbDonVi.SelectedItem).MaDonVi);

                bool isVNI = cheVNI.Checked;
                
                for (int j = dongdautien; j < dongketthuc + 1; j++)
                {
                    List<DTO.DTO_Field> listCDValue = new List<QuanLyCongDan.DTO.DTO_Field>();
                    foreach (DTO.DTO_Field curField in listFieldDonVi)
                    {
                        DTO.DTO_Field CDValue = new QuanLyCongDan.DTO.DTO_Field();
                        CDValue.MaField = curField.MaField;
                        CDValue.TenField = curField.TenField;
                        object cell = ((range.Cells[j, curField.Value + 1] as Excel.Range).Value2);
                        if (cell == null)
                            CDValue.GiaTriThuc = string.Empty;
                        else if(isVNI)
                            CDValue.GiaTriThuc = QuanLyCongDan.UniConverter.TextCodeConverter.VNI2Unicode(cell.ToString());
                        else

                            CDValue.GiaTriThuc = cell.ToString();
                            //CDValue.GiaTriThuc = QuanLyCongDan.UniConverter.TextCodeConverter.VNI2Unicode(cell.ToString());
                        listCDValue.Add(CDValue);
                    }
                    DAL.DAL_CongDan.InsertCongDan(maDonVi, listCDValue);
                }

                xlWorkBook.Close(true, null, null);
                xlApp.Quit();

                MessageBox.Show("Hoàn tất");
                this.DialogResult = DialogResult.OK;
                this.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                MessageBox.Show("Có lỗi xảy ra");
                return;
            }
        }

        private void btnHuyBo_Click(object sender, EventArgs e)
        {
            this.DialogResult = DialogResult.Cancel;
            this.Close();
        }

        private void txtDongDauTien_KeyPress(object sender, KeyPressEventArgs e)
        {
            if (!Char.IsDigit(e.KeyChar) && !Char.IsControl(e.KeyChar))
                e.Handled = true;
        }

        private void cbbDonVi_SelectionChangeCommitted(object sender, EventArgs e)
        {
            MessageBox.Show(((DTO.DTO_DonVi)cbbDonVi.SelectedItem).MaDonVi);
        }
    }
}